﻿
-- ================================================
-- Remarks: 
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Dasideen_ContentStatistics_GetAllContentMetaDataMasterWithContentStaticstics')
	BEGIN
		DROP  Procedure  Dasideen_ContentStatistics_GetAllContentMetaDataMasterWithContentStaticstics
	END

GO

CREATE PROCEDURE [Dasideen_ContentStatistics_GetAllContentMetaDataMasterWithContentStaticstics]
-- =============================================
-- Author:		<Sagar>
-- Create date: <21-04-2015>
-- Description:	<Dasideen_ContentStatistics_GetAllContentMetaDataMasterWithContentStaticstics>
-- =============================================
-- Add the parameters for the stored procedure here
	(
		@Sys_IsDeleted int = 0
	)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT OFF;

--	SELECT     
--	           [ContentRating].[ContentRatingKey],
--			   [ContentMetaDataMaster].[ContentKey],
--			   [ContentMetaDataMaster].[ContentCode],
--			   [ContentMetaDataMaster].[ContentName], 
--			   [ContentMetaDataMaster].[ContentDescription], 
--               [ContentMetaDataMaster].[ContentTypeKey], 
--			   [ContentMetaDataMaster].[ContentTotalDownloads], 
--			   [ContentMetaDataMaster].[ContentTotalLikes], 
--			   [ContentMetaDataMaster].[ContentTotalViews], 
--               [ContentMetaDataMaster].[Sys_DateOfCreation], 
--			   [ContentMetaDataMaster].[Sys_CreatedByID],
--			   AVG
--			   (
--			   [ContentRating].[ContentRatingValue]
--			   ) AS [AvrageRating], 
--			   COUNT
--			   (
--			   [ContentRating].[ContentRatingContentKey]
--			   ) AS [TotalVotes], 
--			   [User].[UserLoginID], 
--			   [User].[UserFirstName], 
--			   [User].[UserLastName]
--FROM           [User] RIGHT OUTER JOIN
--               [ContentMetaDataMaster] 
--ON             [User].[UserKey] = [ContentMetaDataMaster].[Sys_CreatedByID] LEFT OUTER JOIN
--               [ContentRating] 
--ON             [ContentMetaDataMaster].[ContentKey] = [ContentRating].[ContentRatingContentKey]
--GROUP BY       [ContentRating].[ContentRatingContentKey],
--               [ContentMetaDataMaster].[ContentKey],
--               [ContentMetaDataMaster].[ContentCode],
--			   [ContentMetaDataMaster].[ContentName], 
--               [ContentMetaDataMaster].[ContentDescription], 
--			   [ContentMetaDataMaster].[ContentTypeKey], 
--			   [ContentMetaDataMaster].[ContentTotalDownloads],
--			   [ContentMetaDataMaster].[ContentTotalLikes], 
--               [ContentMetaDataMaster].[ContentTotalViews],
--			   [ContentMetaDataMaster].[Sys_DateOfCreation], 
--			   [ContentMetaDataMaster].[Sys_CreatedByID], 
--			   [ContentMetaDataMaster].[Sys_IsDeleted],
--			   [ContentRating].[ContentRatingKey], 
--			   [User].[UserLoginID], 
--               [User].[UserFirstName], 
--			   [User].[UserLastName]
--HAVING			(
--					([ContentMetaDataMaster].[Sys_IsDeleted] = @Sys_IsDeleted)
--				)

	SELECT     
				[ContentMetaDataMaster].[ContentKey], 
				[ContentMetaDataMaster].[ContentCode], 
				[ContentMetaDataMaster].[ContentName], 
				[ContentMetaDataMaster].[ContentMetaTagKeyword], 
				[ContentMetaDataMaster].[ContentMetaTagDescription],
				[ContentMetaDataMaster].[ContentHtmlData], 
				[ContentMetaDataMaster].[ContentTypeKey], 
				[ContentTypeMaster].[ContentTypeCode], 
				[ContentTypeMaster].[ContentTypeName], 
				[ContentMetaDataMaster].[ContentTotalDownloads],
				[ContentMetaDataMaster].[ContentTotalLikes], 
				[ContentMetaDataMaster].[ContentTotalViews], 
				AVG([ContentRating].[ContentRatingValue]) AS [AvrageRating], 
				COUNT([ContentRating].[ContentRatingKey]) AS [TotalVotes], 
				[ContentMetaDataMaster].[Sys_DateOfCreation], 
				[ContentMetaDataMaster].[Sys_CreatedByID], 
				[User].[UserLoginID], 
				[User].[UserFirstName], 
				[User].[UserLastName] 
	FROM 
				[ContentTypeMaster] RIGHT OUTER JOIN [ContentMetaDataMaster] 
	ON			[ContentTypeMaster].[ContentTypeKey] = [ContentMetaDataMaster].[ContentTypeKey] LEFT OUTER JOIN [User] 
	ON			[ContentMetaDataMaster].[Sys_CreatedByID] = [User].[UserKey] LEFT OUTER JOIN [ContentRating] 
	ON			[ContentMetaDataMaster].[ContentKey] = [ContentRating].[ContentRatingContentKey] 
	GROUP BY 
				[ContentMetaDataMaster].[ContentKey], 
				[ContentMetaDataMaster].[ContentCode], 
				[ContentMetaDataMaster].[ContentName], 
				[ContentMetaDataMaster].[ContentMetaTagKeyword], 
				[ContentMetaDataMaster].[ContentMetaTagDescription],
				[ContentMetaDataMaster].[ContentHtmlData], 
				[ContentMetaDataMaster].[ContentTypeKey], 
				[ContentMetaDataMaster].[ContentTotalDownloads], 
				[ContentMetaDataMaster].[ContentTotalLikes], 
				[ContentMetaDataMaster].[ContentTotalViews], 
				[ContentMetaDataMaster].[Sys_DateOfCreation], 
				[ContentMetaDataMaster].[Sys_CreatedByID],
				[ContentMetaDataMaster].[Sys_IsDeleted], 
				[User].[UserLoginID], 
				[User].[UserFirstName], 
				[User].[UserLastName], 
				[ContentTypeMaster].[ContentTypeCode], 
				[ContentTypeMaster].[ContentTypeName] 
	HAVING			
				(
					([ContentMetaDataMaster].[Sys_IsDeleted] = @Sys_IsDeleted)
				)
END
GO
